﻿
--|--------------------------------------------------------------------------------
--| [GetLastGPSPointByGameID] - Insert Procedure Script for GPSPoint
--|--------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id (N'[dbo].[GetLastGPSPointByGameID]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1) 
  DROP PROCEDURE [dbo].[GetLastGPSPointByGameID]
GO

CREATE PROCEDURE [dbo].[GetLastGPSPointByGameID]
(
	@GameID int
)
AS
	SET NOCOUNT ON
	DECLARE @GameName nvarchar(50)
	
	SELECT @GameName = Name FROM Game WHERE ID = @GameID

	SELECT
		[EntityID] as ID,
		[EntityName] as [Name],
		[Latitude],
		[Longitude],
		[Time],
		[Speed],
		[Altitude],
		Datediff(s,P.[Time], GetDate()) as Delay
	FROM [GPSPoint] P
	JOIN (
			-- get last point for marker and game
			SELECT Max(ID) as ID
				FROM GPSPoint 
				WHERE GPSPoint.GameName = @GameName 
				GROUP BY [EntityID]
		) LP ON P.ID = LP.ID
	WHERE
		P.[GameName] = @GameName
		AND [EntityID] IS NOT NULL
--		AND DateDiff(Hour, P.[Time], GetDate()) < 8
	
GO
 